﻿using System.ComponentModel;
using System.Data.OleDb;
using Entity;
using System;

namespace DAO
{
    public class TinRaoVatDAO
    {
        static string connectionString = new connection().getConnectionString();


        public BindingList<TinRaoVat> LoadAll()
        {

            BindingList<TinRaoVat> list = new BindingList<TinRaoVat>();
            //B1: mo ket noi DB
            OleDbConnection connection = new OleDbConnection(connectionString);
            connection.Open();
            //B2: cbi cau truy van
            string sql = "select * from TinRaoVat;";
            //B3: Thuc thi cau truy van
            OleDbCommand command = new OleDbCommand(sql, connection);
            OleDbDataReader reader = command.ExecuteReader();
            //B4: Xu ly ket qua tra ve
            while (reader.Read())
            {
                TinRaoVat kv = new TinRaoVat();
                kv.MaTin = (int)reader["MaTin"];
                kv.TieuDe = (string)reader["TieuDe"];
                kv.MaKhuVuc = (int)reader["MaKhuVuc"];
                kv.NoiDung = (string)reader["NoiDung"];
                kv.NgayTao = (DateTime)reader["NgayTao"];
                kv.NgayHetHan = (DateTime)reader["NgayHetHan"];
                kv.TrangThai = (bool)reader["TrangThai"];
                kv.abuse = (bool)reader["abuse"];
                kv.MaNhom = (int)reader["MaNhom"];
                kv.NguoiTao = (int)reader["NguoiTao"];
                kv.SoLanXem = (double)reader["SoLanXem"];
                list.Add(kv);
            }

            reader.Close();
            //B5: dong ket noi
            connection.Close();
            return list;
        }

        public BindingList<TinRaoVat> LoadAllByNguoiTao(int nguoitao)
        {

            BindingList<TinRaoVat> list = new BindingList<TinRaoVat>();
            //B1: mo ket noi DB
            OleDbConnection connection = new OleDbConnection(connectionString);
            connection.Open();
            //B2: cbi cau truy van
            string sql = "select * from TinRaoVat where NguoiTao = @NguoiTao;";
            //B3: Thuc thi cau truy van
            OleDbCommand command = new OleDbCommand(sql, connection);
            command.Parameters.Add("@NguoiTao", OleDbType.Integer).Value = nguoitao;
            OleDbDataReader reader = command.ExecuteReader();
            //B4: Xu ly ket qua tra ve
            while (reader.Read())
            {
                TinRaoVat kv = new TinRaoVat();
                kv.MaTin = (int)reader["MaTin"];
                kv.TieuDe = (string)reader["TieuDe"];
                kv.MaKhuVuc = (int)reader["MaKhuVuc"];
                kv.NoiDung = (string)reader["NoiDung"];
                kv.NgayTao = (DateTime)reader["NgayTao"];
                kv.NgayHetHan = (DateTime)reader["NgayHetHan"];
                kv.TrangThai = (bool)reader["TrangThai"];
                kv.abuse = (bool)reader["abuse"];
                kv.MaNhom = (int)reader["MaNhom"];
                kv.NguoiTao = (int)reader["NguoiTao"];
                kv.SoLanXem = (double)reader["SoLanXem"];
                list.Add(kv);
            }

            reader.Close();
            //B5: dong ket noi
            connection.Close();
            return list;
        }
        public BindingList<TinRaoVat> LoadAllByNhomTin(int nhomtin)
        {

            BindingList<TinRaoVat> list = new BindingList<TinRaoVat>();
            //B1: mo ket noi DB
            OleDbConnection connection = new OleDbConnection(connectionString);
            connection.Open();
            //B2: cbi cau truy van
            string sql = "select * from TinRaoVat where MaNhom = @MaNhom;";
            //B3: Thuc thi cau truy van
            OleDbCommand command = new OleDbCommand(sql, connection);
            command.Parameters.Add("@MaNhom", OleDbType.Integer).Value = nhomtin;
            OleDbDataReader reader = command.ExecuteReader();
            //B4: Xu ly ket qua tra ve
            while (reader.Read())
            {
                TinRaoVat kv = new TinRaoVat();
                kv.MaTin = (int)reader["MaTin"];
                kv.TieuDe = (string)reader["TieuDe"];
                kv.MaKhuVuc = (int)reader["MaKhuVuc"];
                kv.NoiDung = (string)reader["NoiDung"];
                kv.NgayTao = (DateTime)reader["NgayTao"];
                kv.NgayHetHan = (DateTime)reader["NgayHetHan"];
                kv.TrangThai = (bool)reader["TrangThai"];
                kv.abuse = (bool)reader["abuse"];
                kv.MaNhom = (int)reader["MaNhom"];
                kv.NguoiTao = (int)reader["NguoiTao"];
                kv.SoLanXem = (double)reader["SoLanXem"];
                list.Add(kv);
            }

            reader.Close();
            //B5: dong ket noi
            connection.Close();
            return list;
        }

        public TinRaoVat LoadAllById(int id)
        {

            BindingList<TinRaoVat> list = new BindingList<TinRaoVat>();
            //B1: mo ket noi DB
            OleDbConnection connection = new OleDbConnection(connectionString);
            connection.Open();
            //B2: cbi cau truy van
            string sql = "select * from TinRaoVat where MaTin = @MaTin;";
            //B3: Thuc thi cau truy van
            OleDbCommand command = new OleDbCommand(sql, connection);
            command.Parameters.Add("@MaTin", OleDbType.Integer).Value = id;
            OleDbDataReader reader = command.ExecuteReader();
            //B4: Xu ly ket qua tra ve
            while (reader.Read())
            {
                TinRaoVat kv = new TinRaoVat();
                kv.MaTin = (int)reader["MaTin"];
                kv.TieuDe = (string)reader["TieuDe"];
                kv.MaKhuVuc = (int)reader["MaKhuVuc"];
                kv.NoiDung = (string)reader["NoiDung"];
                kv.NgayTao = (DateTime)reader["NgayTao"];
                kv.NgayHetHan = (DateTime)reader["NgayHetHan"];
                kv.TrangThai = (bool)reader["TrangThai"];
                kv.abuse = (bool)reader["abuse"];
                kv.MaNhom = (int)reader["MaNhom"];
                kv.NguoiTao = (int)reader["NguoiTao"];
                kv.SoLanXem = (double)reader["SoLanXem"];
                list.Add(kv);
            }

            reader.Close();
            //B5: dong ket noi
            connection.Close();
            return list[0];
        }
        public BindingList<TinRaoVat> LoadAbuse()
        {

            BindingList<TinRaoVat> list = new BindingList<TinRaoVat>();
            //B1: mo ket noi DB
            OleDbConnection connection = new OleDbConnection(connectionString);
            connection.Open();
            //B2: cbi cau truy van
            string sql = "select * from TinRaoVat where Abuse = @Abuse;";
            //B3: Thuc thi cau truy van
            OleDbCommand command = new OleDbCommand(sql, connection);
            command.Parameters.Add("@Abuse", OleDbType.Boolean).Value = true;
            OleDbDataReader reader = command.ExecuteReader();
            //B4: Xu ly ket qua tra ve
            while (reader.Read())
            {
                TinRaoVat kv = new TinRaoVat();
                kv.MaTin = (int)reader["MaTin"];
                kv.TieuDe = (string)reader["TieuDe"];
                kv.MaKhuVuc = (int)reader["MaKhuVuc"];
                kv.NoiDung = (string)reader["NoiDung"];
                kv.NgayTao = (DateTime)reader["NgayTao"];
                kv.NgayHetHan = (DateTime)reader["NgayHetHan"];
                kv.TrangThai = (bool)reader["TrangThai"];
                kv.abuse = (bool)reader["abuse"];
                kv.MaNhom = (int)reader["MaNhom"];
                kv.NguoiTao = (int)reader["NguoiTao"];
                kv.SoLanXem = (double)reader["SoLanXem"];
                list.Add(kv);
            }

            reader.Close();
            //B5: dong ket noi
            connection.Close();
            return list;
        }
        public int DeleteByStringId(int id)
        {
            //B1: mo ket noi DB
            OleDbConnection connection = new OleDbConnection(connectionString);
            connection.Open();
            //B2: cbi cau truy van
            string sql = "DELETE FROM TinRaoVat WHERE MaTin = @MaTin;";
            //B3: Thuc thi cau truy van
            OleDbCommand command = new OleDbCommand(sql, connection);
            command.Parameters.Add("@MaTin", OleDbType.VarChar).Value = id;
            int result = command.ExecuteNonQuery();
            //B5: dong ket noi
            connection.Close();

            return result;
        }
        public int ReportAbuse(int id)
        {
            //B1: mo ket noi DB
            OleDbConnection connection = new OleDbConnection(connectionString);
            connection.Open();
            //B2: cbi cau truy van
            string sql = "UPDATE TinRaoVat SET abuse = True WHERE MaTin = @MaTin;";
            //B3: Thuc thi cau truy van
            OleDbCommand command = new OleDbCommand(sql, connection);
            command.Parameters.Add("@MaTin", OleDbType.VarChar).Value = id;
            int result = command.ExecuteNonQuery();
            //B5: dong ket noi
            connection.Close();

            return result;
        }
        public TinRaoVat loadTin(int id)
        {
            BindingList<TinRaoVat> list = new BindingList<TinRaoVat>();
            //B1: mo ket noi DB
            OleDbConnection connection = new OleDbConnection(connectionString);
            connection.Open();
            //B2: cbi cau truy van
            string sql = "select top[1] * from TinRaoVat where MaTin = @MaTin AND TrangThai = True;";
            //B3: Thuc thi cau truy van
            OleDbCommand command = new OleDbCommand(sql, connection);
            OleDbDataReader reader = command.ExecuteReader();
            //B4: Xu ly ket qua tra ve
            while (reader.Read())
            {
                TinRaoVat kv = new TinRaoVat();
                kv.MaTin = (int)reader["MaTin"];
                kv.TieuDe = (string)reader["TieuDe"];
                kv.MaKhuVuc = (int)reader["MaKhuVuc"];
                kv.NoiDung = (string)reader["NoiDung"];
                kv.NgayTao = (DateTime)reader["NgayTao"];
                kv.NgayHetHan = (DateTime)reader["NgayHetHan"];
                kv.TrangThai = (bool)reader["TrangThai"];
                kv.abuse = (bool)reader["abuse"];
                kv.MaNhom = (int)reader["MaNhom"];
                kv.NguoiTao = (int)reader["NguoiTao"];
                kv.SoLanXem = (double)reader["SoLanXem"];
                list.Add(kv);
            }

            reader.Close();
            //B5: dong ket noi
            connection.Close();
            return list[0];
        }
        public int Insert(TinRaoVat kv)
        {
            //B1: mo ket noi DB
            OleDbConnection connection = new OleDbConnection(connectionString);
            connection.Open();
            //B2: cbi cau truy van
            string sql = "INSERT INTO TinRaoVat ([TieuDe],[MaKhuVuc],[NoiDung],[NgayTao],[NgayHetHan],[TrangThai],[abuse],[MaNhom],[NguoiTao],[SoLanXem]) VALUES(@TieuDe,@MaKhuVuc,@NoiDung,@NgayTao,@NgayHetHan,@TrangThai,@abuse,@MaNhom,@NguoiTao,@SoLanXem);";
            //B3: Thuc thi cau truy van
            OleDbCommand command = new OleDbCommand(sql, connection);
            command.Parameters.Add("@TieuDe", OleDbType.VarChar).Value = kv.TieuDe;
            command.Parameters.Add("@MaKhuVuc", OleDbType.Integer).Value = kv.MaKhuVuc;
            command.Parameters.Add("@NoiDung", OleDbType.VarChar).Value = kv.NoiDung;
            command.Parameters.Add("@NgayTao", OleDbType.Date).Value = kv.NgayTao;
            command.Parameters.Add("@NgayHetHan", OleDbType.Date).Value = kv.NgayHetHan;
            command.Parameters.Add("@TrangThai", OleDbType.Boolean).Value = kv.TrangThai;
            command.Parameters.Add("@abuse", OleDbType.Boolean).Value = kv.abuse;
            command.Parameters.Add("@MaNhom", OleDbType.Integer).Value = kv.MaNhom;
            command.Parameters.Add("@NguoiTao", OleDbType.Integer).Value = kv.NguoiTao;
            command.Parameters.Add("@SoLanXem", OleDbType.Double).Value = kv.SoLanXem;
            int result = command.ExecuteNonQuery();
            //B5: dong ket noi
            connection.Close();
            return result;
        }
        public int Update(TinRaoVat kv)
        {
            //B1: mo ket noi DB
            OleDbConnection connection = new OleDbConnection(connectionString);
            connection.Open();
            //B2: cbi cau truy van
            string sql = "UPDATE TinRaoVat SET [TieuDe] = @TieuDe,[MaKhuVuc] = @MaKhuVuc,[NoiDung] = @NoiDung,[NgayTao] = @NgayTao,[NgayHetHan] = @NgayHetHan,[TrangThai] = @TrangThai,[abuse] = @abuse,[MaNhom] = @MaNhom,[NguoiTao] = @NguoiTao,[SoLanXem] = @SoLanXem WHERE MaTin = @MaTin;";

            //B3: Thuc thi cau truy van
            OleDbCommand command = new OleDbCommand(sql, connection);
            command.Parameters.Add("@TieuDe", OleDbType.VarChar).Value = kv.TieuDe;
            command.Parameters.Add("@MaKhuVuc", OleDbType.Integer).Value = kv.MaKhuVuc;
            command.Parameters.Add("@NoiDung", OleDbType.VarChar).Value = kv.NoiDung;
            command.Parameters.Add("@NgayTao", OleDbType.Date).Value = kv.NgayTao;
            command.Parameters.Add("@NgayHetHan", OleDbType.Date).Value = kv.NgayHetHan;
            command.Parameters.Add("@TrangThai", OleDbType.Boolean).Value = kv.TrangThai;
            command.Parameters.Add("@abuse", OleDbType.Boolean).Value = kv.abuse;
            command.Parameters.Add("@MaNhom", OleDbType.Integer).Value = kv.MaNhom;
            command.Parameters.Add("@NguoiTao", OleDbType.Integer).Value = kv.NguoiTao;
            command.Parameters.Add("@SoLanXem", OleDbType.Double).Value = kv.SoLanXem;

            command.Parameters.Add("@MaTin", OleDbType.VarChar).Value = kv.MaTin;
            int result = command.ExecuteNonQuery();
            //B5: dong ket noi
            connection.Close();
            return result;
        }
    }
}
